ALTER PROC sp_save_sales_forecast ( 
    	@rowId int,
	@salesPersonId int,
	@prodId int,
	@custId int,
	@odmId int,
	@projId int,
	@month int,
	@quarter int,
	@year int,
	@qty int,
	@price float,
	@confidenceLevel float,
	@userList1 varchar(100),
	@userDate1 datetime,
	@userTxt1 varchar(100),
	@userTxt2 varchar(100),
	@userId varchar(20),
	@success int OUTPUT ) AS

BEGIN
	DECLARE @DB_QTY	INT
	DECLARE @DB_PRICE FLOAT
	DECLARE @DB_CONFIDENCE_LEVEL FLOAT
	DECLARE @INPUT_PRICE	FLOAT

	SET @INPUT_PRICE = @price

	IF (@price = 0.0 OR @price IS NULL )
	BEGIN
		DECLARE @PULL_TYPE INT
		SELECT @PULL_TYPE = ISNULL(PRICE_PULL,1) FROM SYSTEMPRFS		

		IF (@PULL_TYPE = 1)
		BEGIN
			SELECT @price = PPT_Unit_Price from Product_Price_Table where Product_ID = @prodId and (@qty between PPT_Qty_From and PPT_Qty_To)
		END
		ELSE IF (@PULL_TYPE = 2)
		BEGIN
			SELECT @price = CPT_Unit_Price from Customer_Pricing_Table where Product_ID = @prodId and Customer_ID = @odmId and (@qty between CPT_Qty_From and CPT_Qty_To)
		END
	END
	
	/*IF QUANTITY ENTERED IS ZERO CONFIDENCE LEVEL SHOULD BE RESET*/
	/*IF ( @qty = 0 )
	 BEGIN
		SET @confidenceLevel = 0.0
	 END*/
	SET @rowId = NULL --GETTING ROW ID AGAIN TO RESOLVE PAGE REFRESH PROBLEM
	SELECT @rowId = RowId FROM FCENTRYMASTER WHERE SPersonId = @salesPersonId AND ProdId = @prodId AND ProjId = @projId AND Month = @month AND Year  = @year
	IF ( @rowId IS NULL )
	 BEGIN
		SET @rowId = 0
	 END
	SET @success = 1
	IF ( @rowId > 0 )
	 BEGIN		
		--get old data
		SELECT @DB_QTY = Qty, @DB_PRICE = Price, @DB_CONFIDENCE_LEVEL = Confidence_Level FROM FCENTRYMASTER WHERE RowId = @rowId
		--check for difference
		IF ( @DB_QTY <> @qty OR @DB_PRICE <> @INPUT_PRICE OR @DB_CONFIDENCE_LEVEL <> @confidenceLevel )
		 BEGIN
			--copy to history table
			INSERT INTO FCENTRYHISTORY ( RowId, SPersonId, ProdId, CustId, OdmId,ProjId, 
						Month, Quarter, Year, Qty, Price,Confidence_level, UserList1, 
						UserDate1, UserTxt1,UserTxt2, UserId, EntryDate )
				( SELECT RowId, SPersonId, ProdId, CustId, OdmId,ProjId,Month, Quarter, Year, 
					Qty, Price,Confidence_level, UserList1, UserDate1, UserTxt1,UserTxt2, 
					UserId, EntryDate FROM FCENTRYMASTER WHERE RowId = @rowId )
			--now update
			UPDATE FCENTRYMASTER SET SPersonId = @salesPersonId, ProdId = @prodId, CustId = @custId,
						OdmId = @odmId, ProjId = @projId, Month = @month, Quarter = @quarter,
						Year = @year, Qty = @qty, Price = @price, Confidence_level = @confidenceLevel,
						UserList1 = @userList1,UserDate1 = @userDate1,UserTxt1 = @userTxt1,
						UserTxt2 = @userTxt2, UserId = @userId, EntryDate = CURRENT_TIMESTAMP
					    WHERE RowId = @rowId
			IF ( @@ROWCOUNT = 0 )
			 BEGIN
				SET @success = 1 --false
			 END
			ELSE
			 BEGIN
				SET @success = 0 --true
			 END
		 END
	 END
	ELSE
	 BEGIN
		IF ( @qty > 0 OR @price > 0 OR @confidenceLevel > 0 )
		 BEGIN
			--insert in master
			INSERT INTO FCENTRYMASTER ( SPersonId, ProdId, CustId, OdmId,ProjId, 
						Month, Quarter, Year, Qty, Price,Confidence_level, UserList1, 
						UserDate1, UserTxt1,UserTxt2, UserId, EntryDate )
			VALUES
				( @salesPersonId ,@prodId ,@custId ,@odmId ,@projId ,@month ,@quarter ,@year ,@qty ,@price,
				  @confidenceLevel ,@userList1 ,@userDate1 ,@userTxt1 ,@userTxt2 ,@userId, CURRENT_TIMESTAMP )
			IF ( @@ROWCOUNT = 0 )
			 BEGIN
				SET @success = 1 --false
			 END
			ELSE
			 BEGIN
				SET @success = 0 --true
			 END
			--insert in history
			INSERT INTO FCENTRYHISTORY ( RowId, SPersonId, ProdId, CustId, OdmId,ProjId, 
						Month, Quarter, Year, Qty, Price,Confidence_level, UserList1, 
						UserDate1, UserTxt1,UserTxt2, UserId, EntryDate )
				( SELECT RowId, SPersonId, ProdId, CustId, OdmId,ProjId,Month, Quarter, Year, Qty, 
					Price,Confidence_level, UserList1, UserDate1,UserTxt1,UserTxt2, UserId, 
					EntryDate FROM FCENTRYMASTER WHERE RowId = @@IDENTITY )
		 END
	 END
END

